package com.bawei.persona.analyse.mapper;


import com.bawei.persona.analyse.bean.ProtalStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;



/**
 * 项目规划及管理
 * 上海大数据学院院长 ：孙丰朝
 * 技术指导及需求分析：郭洵
 * 编程：楚志高
 *
 * @author bawei  bigdata sh
 * @since 2021-06-11
 */

public interface ProtalPersonPaymentMapper {

    //获取按年龄段进行销售额度的比重分析
    //下面的统计是单一的维度的进行统计

    // 得到的是基于订单的统计
    @Select("SELECT yearbasetype,yearbasename, sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  yearbasetype, yearbasename HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getTotalamountByYea1();
    @Select(" SELECT user_gendertype,  user_gender, sum(payment_total_amount) AS payment_total_amount  FROM protrait_payment\n  " +
            "  GROUP BY   user_gendertype, user_gender HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getTotalamountBySex1();
    @Select(" select province_name , sum(payment_total_amount) as payment_total_amount  from protrait_payment   " +
            "  group by  province_name  ")
    List<ProtalStats> getTotalamountByArea1();
    @Select(" SELECT carrier,  carriername,  sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   carrier,  carriername HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByCarry1();
    @Select("  SELECT  email,  sum(payment_total_amount) AS payment_total_amount FROM protrait_payment " +
            "  GROUP BY email HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC LIMIT 100 ")
    List<ProtalStats> getTotalamountByEmal1();
    @Select(" select category1_id , category1_name ,sum(payment_total_amount) as payment_total_amount     " +
            "  from protrait_payment group by  category1_id ,category1_name having payment_total_amount >0  order by payment_total_amount desc  limit 10   ")
    List<ProtalStats> getTotalamountByCate11();
    @Select("  select category2_id , category2_name ,sum(payment_total_amount) as payment_total_amount    from protrait_payment group by  category2_id ,category2_name  " +
            " having payment_total_amount >0  order by payment_total_amount desc  limit 10   ")
    List<ProtalStats> getTotalamountByCate21();
    @Select(" select category3_id , category3_name ,sum(payment_total_amount) as payment_total_amount      from protrait_payment group by  category3_id ,category3_name   " +
            "  having payment_total_amount >0  order by payment_total_amount desc  ")
    List<ProtalStats> getTotalamountByCate31();
    @Select("  SELECT  tm_name,  sum(payment_total_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY tm_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmname1();
    //下面的维度统计是按2个维度进行统计
    @Select("  select user_gender , yearbasename,  sum(payment_total_amount)  as payment_total_amount from protrait_payment  group by yearbasename , user_gender  " +
            " order by payment_total_amount desc   ")
    List<ProtalStats> getTotalamountByYearSex1();
    @Select(" SELECT yearbasetype,yearbasename, province_name, sum(payment_total_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY  yearbasetype, yearbasename, province_name  HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByYearArea1();
    @Select(" SELECT carriername,yearbasename, sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   yearbasename ,carriername HAVING payment_total_amount > 0 ORDER BY  carriername,payment_total_amount DESC   ")
    List<ProtalStats> getTotalamountByCarryYear1();
    @Select("  SELECT carriername,province_name, sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   carriername ,province_name HAVING payment_total_amount > 0 ORDER BY  carriername,payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByCarryArea1();
    @Select("select  carriername,  user_gender ,sum(payment_total_amount)  as payment_total_amount from protrait_payment  group by carriername , user_gender     " +
            " order by carriername, payment_total_amount desc   ")
    List<ProtalStats> getTotalamountByCarrySex1();
    //品牌对人性的带货
    @Select("  SELECT tm_name, user_gender, sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   tm_name ,user_gender HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameSex1();
    @Select(" SELECT tm_name,yearbasename, sum(payment_total_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY   tm_name ,yearbasename HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameYear();
    @Select("  SELECT tm_name, province_name, sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   tm_name ,province_name HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameArea1();
    //人性3个维度的组合
    @Select("  SELECT province_name, yearbasename, user_gender,  sum(payment_total_amount) AS payment_total_amount   " +
            "  FROM protrait_payment GROUP BY  yearbasename, user_gender, province_name HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC  ")
    List<ProtalStats> getTotalamountByYearSexArea1();

    //////////////////////////////////////////////////////////////////
    //首单减免与折扣减免的用户画像只与人性有关,与运营商，与品牌可能绑定
    @Select("SELECT  yearbasename,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment    " +
            "  GROUP BY yearbasename HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getCouponByYea1();
    @Select("  SELECT  user_gender,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY user_gender HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getCouponBySex1();
    @Select("  SELECT  province_name,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY province_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getCouponByArea1();
    @Select(" SELECT  carriername,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment   " +
            " GROUP BY carriername HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getCouponByCarry1();
    @Select(" SELECT  tm_name,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment   " +
            "  GROUP BY tm_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getCouponByTmname1();
    @Select("  SELECT user_gender, yearbasename,  sum(coupon_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY  yearbasename, user_gender   HAVING payment_total_amount > 0 ORDER BY  user_gender,payment_total_amount DESC   ")
    List<ProtalStats> getCouponByYearSex1();
    @Select("  SELECT province_name, yearbasename,  sum(coupon_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  yearbasename, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC   ")
    List<ProtalStats> getCouponByYearArea1();
    @Select("  SELECT province_name, user_gender,  sum(coupon_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  user_gender, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC   ")
    List<ProtalStats> getCouponBySexArea();
    @Select(" SELECT province_name, yearbasename , user_gender,  sum(coupon_reduce_amount) AS payment_total_amount    " +
            "  FROM protrait_payment GROUP BY   yearbasename ,user_gender, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC  ")
    List<ProtalStats> getCouponByYearSexArea1();
    //系统按电商的分类级别进行统计用户画像
    @Select("  SELECT  category1_name,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            "   GROUP BY category1_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getCouponByByCate11();
    @Select(" SELECT  category2_name,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            "  GROUP BY category2_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getCouponByByCate21();
    @Select("  SELECT  category3_name,  sum(coupon_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY category3_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC   ")
    List<ProtalStats> getCouponByByCate3();
    //折扣看品牌与人性结合进行减免可能减免
    @Select(" SELECT tm_name, yearbasename , sum(coupon_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY   tm_name ,yearbasename   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getCouponByTmnameYea1();
    @Select(" SELECT tm_name, user_gender , sum(coupon_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY   tm_name ,user_gender   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getCouponByTmnamSex1();
    @Select("  SELECT tm_name, province_name , sum(coupon_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   tm_name ,province_name   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getCouponByTmnameArea1();

    /////////////////////////////////////////////////////////
    // 折扣针对人性，电商配合平台分类，品牌针对人性进行折扣活动

    @Select("SELECT  yearbasename,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment    " +
            "  GROUP BY yearbasename HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByYea1();
    @Select("  SELECT  user_gender,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY user_gender HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getActivtyBySex1();
    @Select("  SELECT  province_name,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY province_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByArea1();
    @Select(" SELECT  carriername,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment   " +
            " GROUP BY carriername HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByCarry1();
    @Select(" SELECT  tm_name,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment   " +
            "  GROUP BY tm_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByTmname1();
    @Select("  SELECT user_gender, yearbasename,  sum(activity_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY  yearbasename, user_gender   HAVING payment_total_amount > 0 ORDER BY  user_gender,payment_total_amount DESC   ")
    List<ProtalStats> getActivtyByYearSex1();
    @Select("  SELECT province_name, yearbasename,  sum(activity_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  yearbasename, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC   ")
    List<ProtalStats> getActivtyByYearArea1();
    @Select("  SELECT province_name, user_gender,  sum(activity_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  user_gender, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC   ")
    List<ProtalStats> getActivtyBySexArea1();
    @Select(" SELECT province_name, yearbasename , user_gender,  sum(activity_reduce_amount) AS payment_total_amount    " +
            "  FROM protrait_payment GROUP BY   yearbasename ,user_gender, province_name   HAVING payment_total_amount > 0 ORDER BY  province_name,payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByYearSexArea1();
    //系统按电商的分类级别进行统计用户画像
    @Select("  SELECT  category1_name,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            "   GROUP BY category1_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getActivtyByByCate11();
    @Select(" SELECT  category2_name,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            "  GROUP BY category2_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC ")
    List<ProtalStats> getActivtyByByCate21();
    @Select("  SELECT  category3_name,  sum(activity_reduce_amount) AS payment_total_amount FROM protrait_payment  " +
            " GROUP BY category3_name HAVING payment_total_amount > 0 ORDER BY payment_total_amount DESC   ")
    List<ProtalStats> getActivtyByByCate31();
    //折扣看品牌与人性结合进行减免可能减免
    @Select(" SELECT tm_name, yearbasename , sum(activity_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY   tm_name ,yearbasename   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnameYea1();
    @Select(" SELECT tm_name, user_gender , sum(activity_reduce_amount) AS payment_total_amount   " +
            " FROM protrait_payment GROUP BY   tm_name ,user_gender   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnamSex1();
    @Select("  SELECT tm_name, province_name , sum(activity_reduce_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY   tm_name ,province_name   HAVING payment_total_amount > 0 ORDER BY  tm_name,payment_total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnameArea1();

    @Select(" SELECT yearbasename,substr(spu_name,1,40) as spu_name ,sum(sku_num) as sku_num ,max(order_price) as price ,sum(payment_total_amount) AS payment_total_amount  " +
            " FROM protrait_payment GROUP BY  spu_name,yearbasename  HAVING payment_total_amount > 0 ORDER BY yearbasename, payment_total_amount DESC  ")

    List<ProtalStats> getGoodTop1();

}